package com.lt.contract.bean.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.context.annotation.Bean;
import org.apache.poi.ss.usermodel.CellType;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelImport {
    /**
     * 判断文件格式
     *
     * @param inStr
     * @param fileName
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(inStr);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(inStr);
        } else {
            throw new Exception("请上传excel文件！");
        }
        inStr.close();
        return workbook;
    }


    /**
     * 验证excel是否全部为空
     * @param row 当前行
     * @param firstRow 第一行标题行
     * @return
     */
    public static boolean isAllRowEmpty(Row row, Row firstRow) {
        int count = 0;
        //单元格数量
        int rowCount = firstRow.getLastCellNum() - firstRow.getFirstCellNum();
        //判断多少个单元格为空
        for (int c = 0; c < rowCount; c++) {
            Cell cell = row.getCell(c);
            if (cell == null ||  StringUtils.isEmpty((cell+"").trim())){
                count += 1;
            }
        }
        if (count == rowCount) {
            return true;
        }

        return  false;
    }

    /**
     * 判断单元格为空
     * @return
     */
    public static boolean isAllCellEmpty(Cell cell) {
        int count = 0;
        //单元格数量
       if (cell == null ||ExcelImport.getCellValue(cell)==null || cell.toString().equals("")){
           return  false;
       }else {
           return true;
       }
    }



    /**
     * 对Excel的各个单元格的格式进行判断并转换
     */
    public static String getCellValue(Cell cell) {
        String value = "";
        DecimalFormat df = new DecimalFormat("#.00");
        if(cell == null){
            return value;
        }
        switch (cell.getCellType()) {
            case STRING:value = cell.getStringCellValue();
                value =cell.getRichStringCellValue().getString().trim();
                break;
            case NUMERIC:value = "" + cell.getNumericCellValue();
                value =df.format(cell.getNumericCellValue()).toString();
                String valueS =value.substring(value.indexOf("."));
                if(valueS.equals(".00")){
                    value = value.substring(0,value.indexOf("."));
                }
                if(value ==null){
                    value="0";
                }
                break;
            case BOOLEAN:value= "" + cell.getCellFormula();
                value =String.valueOf(cell.getBooleanCellValue()).trim();
                break;
            case FORMULA:value = "" + cell.getCellFormula();
                value =cell.getCellFormula();
                break;
            default:
                value = "";
        }
        return value;
    }
    //判断用户上传的List中是否与数据库重复
    public static List<List<String>> queryRepetition(Sheet sheet, int index) throws Exception{
        List<List<String>> lists = new ArrayList<>();
        List<String> list = new ArrayList<>();
        List<String> recur = new ArrayList<>();
        for (int j = sheet.getFirstRowNum() + 1; j <= sheet.getLastRowNum()+1; j++) {
            if(list.size()>900 || j==sheet.getLastRowNum()+1){
                List<String> copy = new ArrayList<>(list);
                lists.add(copy);
                list.clear();
            }
            Row row = sheet.getRow(j);
            if (row == null || row.getFirstCellNum() == j ) {
                continue;
            }else if (ExcelImport.isAllRowEmpty(row,row)){
                continue;
            }
            String serialNumber = ExcelImport.getCellValue(row.getCell(index));
            if(list.contains(serialNumber)){
                recur.add(serialNumber);
            }
            list.add(serialNumber);
        }

        return lists;
    }


    //判断用户上传的List中是否与数据库重复
    public static String isEmpty(Sheet sheet, List<Integer> list) throws Exception{
        for (int j = sheet.getFirstRowNum() + 1; j <= sheet.getLastRowNum()+1; j++) {
            Row row = sheet.getRow(j);
            if (row == null || row.getFirstCellNum() == j ) {
                continue;
            }else if (ExcelImport.isAllRowEmpty(row,row)){
                continue;
            }
            for (int i = 0; i <list.size() ; i++) {
                Cell cell = row.getCell(list.get(i));
                if(!isAllCellEmpty(cell)){
                    String col = ExcelColumn.excelColIndexToStr(list.get(i)+1);
                    return "您导入的模板中"+col+(j+1)+"单元格为空，请确认";
                }
            }
        }
        return "true";
    }

    //判断用户上传的List中是否有重复
    public static List<String> isRecur(Sheet sheet, int index) throws Exception{
        List<String> list = new ArrayList<>();
        List<String> recur = new ArrayList<>();
        for (int j = sheet.getFirstRowNum() + 1; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);
            if (row == null || row.getFirstCellNum() == j || row.getCell(1) == null) {
                continue;
            }else if (ExcelImport.isAllRowEmpty(row,row)){
                continue;
            }
            String serialNumber = ExcelImport.getCellValue(row.getCell(index));
            if(list.contains(serialNumber)){
                recur.add(serialNumber);
            }
            list.add(serialNumber);
        }
        return recur;
    }
}
